package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import model.bean.Account;

public class ManageAccountDAO extends BaseDAO{

	Connection connection = null;
	
	/**
	 * 
	 * @param partID 
	 * @return list account
	 */
	public ArrayList<Account> getListAccount(int partID) {
		
		ArrayList<Account> listAccount = new ArrayList<Account>();
		connection = getConnection();
		String sqlSelect = "SELECT TenTaiKhoan, MatKhau, TAIKHOAN.MaLoaiTK, HoTen, NgaySinh, SDT, " +
				"Email, GioiTinh, TenLoaiTK, Avatar " +
				"FROM TAIKHOAN JOIN LOAITAIKHOAN ON TAIKHOAN.MaLoaiTK = LOAITAIKHOAN.MaLoaiTK";
		if(partID != 0){
		    sqlSelect += " WHERE TAIKHOAN.MaLoaiTK = " + partID;
		}
		if(connection != null) {
			try {
				PreparedStatement pstm = connection.prepareStatement(sqlSelect);
				ResultSet result = pstm.executeQuery();
				Account account = null;
				while(result.next()){
					   account = new Account();
					   account.setNameAccount(result.getString(1));
                       account.setPassword(result.getString(2));
                       account.setPrivilege(result.getInt(3));
                       account.setName(result.getString(4));
                       account.setDateOfBirth(result.getString(5));
                       account.setPhoneNumber(result.getString(6));
                       account.setEmail(result.getString(7));
                       account.setSex(result.getBoolean(8));
                       account.setDesAccount(result.getString(9));
                       account.setAvatar(result.getString(10));
	                   listAccount.add(account);
				}
				return listAccount;
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			} finally {
				closeConnection();
			}
		}else{
			return null;
		}
	}
	
	/**
	 * 
	 * @param nameAccount
	 * @return Account
	 */
	public Account getAccount(String nameAccount){
		
		connection = getConnection();
		
		if(connection != null) {
			String sqlSelect = "SELECT TenTaiKhoan, MatKhau, TAIKHOAN.MaLoaiTK, HoTen, NgaySinh, SDT, Email, GioiTinh, TenLoaiTK, Avatar FROM TAIKHOAN JOIN LOAITAIKHOAN ON TAIKHOAN.MaLoaiTK = LOAITAIKHOAN.MaLoaiTK WHERE TenTaiKhoan = '"+ nameAccount + "'";
			try {
				PreparedStatement pstm = connection.prepareStatement(sqlSelect);
				ResultSet result = pstm.executeQuery();
				Account account = null;
				if(result.next()){
					   account = new Account();
	                   account.setNameAccount(result.getString(1));
	                   account.setPassword(result.getString(2));
	                   account.setPrivilege(result.getInt(3));
	                   account.setName(result.getNString(4));
	                   account.setDateOfBirth(result.getString(5));
	                   account.setPhoneNumber(result.getString(6));
	                   account.setEmail(result.getString(7));
	                   account.setSex(result.getBoolean(8));
	                   account.setDesAccount(result.getNString(9));
	                   account.setAvatar(result.getString(10));
				}
				return account;
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			} finally {
				closeConnection();
			}
		}else{
			return null;
		}
	}
	/**
	 * 
	 * @param accountName
	 * @return number of row deleted
	 */
	public int deleteAccount(String accountName) {
		
		connection = getConnection();
		int rowAffect = 0;
		
		if(connection != null) {
			try{
			 	String sqlDelete = "DELETE FROM TAIKHOAN WHERE TenTaiKhoan = ?";
				PreparedStatement pstm = connection.prepareStatement(sqlDelete);
				pstm.setString(1, accountName);
				return  pstm.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				return rowAffect;
			} finally {
				closeConnection();
			}
		}else{
			return rowAffect;
		}
	}
	/**
	 * @param account
	 * @param newPass
	 * @return number of row effect
	 */
	public int changePassword(Account account, String newPass){
		
		connection = getConnection();
		int rowAffect = 0;
		
		if(connection != null) {
			try{
			 	String sql = "UPDATE TAIKHOAN SET MatKhau = ? WHERE TenTaiKhoan = ?";
				PreparedStatement pstm = connection.prepareStatement(sql);
				pstm.setString(1, newPass);
				pstm.setString(2, account.getNameAccount());
				return  pstm.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				return rowAffect;
			} finally {
				closeConnection();
			}
		}else{
			return rowAffect;
		}
	}
	
	/**
	 * 
	 * @param account
	 * @return 
	 */
	public int updateInfoAccount(Account account){
		
		connection = getConnection();
		int rowAffect = 0;
		
		if(connection != null) {
			try{
				String sql = "UPDATE TAIKHOAN SET HoTen = ?, NgaySinh = ?, SDT = ?, Email = ?, GioiTinh = ?, Avatar = ? WHERE TenTaiKhoan = ?";
				PreparedStatement pstm = connection.prepareStatement(sql);
				pstm.setString(7, account.getNameAccount());
				pstm.setString(1, account.getName());
				pstm.setString(2, account.getDateOfBirth());
				pstm.setString(3, account.getPhoneNumber());
				pstm.setString(4, account.getEmail());
				pstm.setBoolean(5, account.isSex());
				if(account.getAvatar() != null){
				        pstm.setString(6, account.getAvatar());
				}else{
				    pstm.setString(6, "NoAvatar");
				}
				return  pstm.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				return rowAffect;
			} finally {
				closeConnection();
			}
		}else{
			return rowAffect;
		}
	}
	
	/**
	 * 
	 * @param account
	 * @return 
	 */
	public int insertAccount(Account account){
		connection = getConnection();
		int rowAffect = 0;
		
		if(connection != null) {
			try{
			 	String sql = "INSERT INTO TAIKHOAN VALUES(?,?,?,?,?,?,?,?,?)";
				PreparedStatement pstm = connection.prepareStatement(sql);
				pstm.setString(1, account.getNameAccount());
				pstm.setString(2, account.getPassword());
				pstm.setInt(3, account.getPrivilege());
				pstm.setNString(4, account.getName());
				pstm.setString(5, account.getDateOfBirth());
				pstm.setString(6, account.getPhoneNumber());
				pstm.setString(7, account.getEmail());
				pstm.setBoolean(8, account.isSex());
				pstm.setString(9, "");
				return  pstm.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				return rowAffect;
			} finally {
				closeConnection();
			}
		}else{
			return rowAffect;
		}
	}
	
	/**
	 * 
	 * @param account
	 * @return
	 */
	public int updateAccount(Account account){
		
		connection = getConnection();
		int rowAffect = 0;
		
		if(connection != null) {
			try{
				String sql = "UPDATE TAIKHOAN SET MatKhau = ? MaLoaiTK = ?, HoTen = ?, NgaySinh = ?, SDT = ?, Email = ?, GioiTinh = ? WHERE TenTaiKhoan = ?";
				PreparedStatement pstm = connection.prepareStatement(sql);
				pstm.setString(9, account.getNameAccount());
				pstm.setString(1, account.getPassword());
				pstm.setInt(2, account.getPrivilege());
				pstm.setString(3, account.getName());
				pstm.setString(4, account.getDateOfBirth());
				pstm.setString(5, account.getPhoneNumber());
				pstm.setString(6, account.getEmail());
				pstm.setBoolean(7, account.isSex());
				return  pstm.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				return rowAffect;
			} finally {
				closeConnection();
			}
		}else{
			return rowAffect;
		}
	}

}
